package com.neelsys.visualisation.service.persist;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.FileSystemResource;
import org.springframework.data.neo4j.template.Neo4jOperations;
import org.springframework.stereotype.Service;

import com.neelsys.visualisation.domain.CourseDesignator;
import com.neelsys.visualisation.domain.CourseLevelEnum;
import com.neelsys.visualisation.domain.CourseStreamEnum;
import com.neelsys.visualisation.domain.EntranceTest;
import com.neelsys.visualisation.domain.Jobs;
import com.neelsys.visualisation.domain.MajorCombination;
import com.neelsys.visualisation.repository.CourseDesignatorRepository;
import com.neelsys.visualisation.repository.EntranceTestRepository;
import com.neelsys.visualisation.repository.JobsRepository;
import com.neelsys.visualisation.repository.majorComb.MajorCombinationRepository;

@Service
public class PersistNodesFromExcel {

	private static XSSFWorkbook workbook;

	static {
		try {
			FileSystemResource resource = new FileSystemResource(
					"D:\\Vis_Data.xlsx");
			File f = resource.getFile();
			FileInputStream file = new FileInputStream(f);
			workbook = new XSSFWorkbook(file);
		} catch (Exception e) {
			throw new RuntimeException("Something Wrong in reading data file:",
					e);
		}

	}

	@Autowired
	private Neo4jOperations template;

	@Autowired
	private MajorCombinationRepository majorCombRepo;

	@Autowired
	private CourseDesignatorRepository courseDesigRepo;

	@Autowired
	private EntranceTestRepository entranceTestRepo;

	@Autowired
	private JobsRepository jobRepository;

	public PersistNodesFromExcel() {

	}

	public void persist() {

		System.out.println("WorkBook Save Started..!!");

		persistCourseDesignators();

		XSSFSheet majorSheet = workbook.getSheet("Major Book");
		persistMajors(majorSheet);

		XSSFSheet entranceSheet = workbook.getSheet("Entrance Book");
		persistEntranceTests(entranceSheet);

		XSSFSheet jobSheet = workbook.getSheet("Job Book");
		persistJobs(jobSheet);
		

		createRelationShips();

		System.out.println("WorkBook Save Ended..!!");

	}

	private void persistCourseDesignators() {

		for (CourseLevelEnum courseLevel : CourseLevelEnum.values()) {

			for (CourseStreamEnum courseStream : CourseStreamEnum.values()) {
				CourseDesignator courseDesignator = new CourseDesignator();
				courseDesignator.setCourseLevel(courseLevel);
				courseDesignator.setCourseStream(courseStream);
				template.save(courseDesignator);
			}

		}
	}

	private CourseDesignator getCourseDesig(String courseLevel,
			String courseStream) {

		CourseLevelEnum courseLevelEnum = CourseLevelEnum.valueOf(courseLevel);
		CourseStreamEnum courseStreamEnum = CourseStreamEnum
				.valueOf(courseStream);

		return getCourseDesig(courseLevelEnum, courseStreamEnum);
	}

	private CourseDesignator getCourseDesig(CourseLevelEnum courseLevelEnum,
			CourseStreamEnum courseStreamEnum) {

		CourseDesignator courseDesignator = courseDesigRepo
				.findByCourseLevelAndCourseStream(courseLevelEnum,
						courseStreamEnum);

		return courseDesignator;
	}

	private void persistMajors(XSSFSheet majorSheet) {

		Iterator<Row> rowIt = majorSheet.iterator();

		rowIt.next();

		while (rowIt.hasNext()) {
			Row curRow = rowIt.next();

			Iterator<Cell> colIt = curRow.cellIterator();

			String courseLevelStr = colIt.next().getStringCellValue();

			if (StringUtils.isBlank(courseLevelStr)) {
				return;
			}

			CourseDesignator courseDesig = getCourseDesig(courseLevelStr, colIt
					.next().getStringCellValue());

			String majorName = colIt.next().getStringCellValue();
			String courseName = colIt.next().getStringCellValue();
			MajorCombination majorCombination = new MajorCombination();

			majorCombination.setCourseIdentifier(courseName);
			majorCombination.setMajorIdentifier(majorName);
			majorCombination.setCourseDesigntator(courseDesig);
			majorCombination.setMajorDescription(colIt.next()
					.getStringCellValue());
			majorCombination.setHasEntranceTest(colIt.next()
					.getBooleanCellValue());
			majorCombination.setMinDuration(String.valueOf(colIt.next()
					.getNumericCellValue()));
			template.save(majorCombination);

		}

	}

	private void persistEntranceTests(XSSFSheet entranceSheet) {

		Iterator<Row> rowIt = entranceSheet.iterator();

		rowIt.next();

		while (rowIt.hasNext()) {
			Row curRow = rowIt.next();

			Iterator<Cell> colIt = curRow.cellIterator();

			String courseLevelStr = colIt.next().getStringCellValue();

			if (StringUtils.isBlank(courseLevelStr)) {
				return;
			}

			CourseDesignator courseDesig = getCourseDesig(courseLevelStr, colIt
					.next().getStringCellValue());

			String testName = colIt.next().getStringCellValue();

			EntranceTest entranceTest = new EntranceTest();

			entranceTest.setEntranceTestDetails(testName);
			entranceTest.setCourseDesigntator(courseDesig);
			entranceTest.setMaximumRetries(String.valueOf(colIt.next()
					.getNumericCellValue()));
			entranceTest.setDescription(colIt.next().getStringCellValue());
			entranceTest.setForCourse(colIt.next().getStringCellValue());

			template.save(entranceTest);

		}
	}

	private void persistJobs(XSSFSheet majorSheet) {

		Iterator<Row> rowIt = majorSheet.iterator();

		rowIt.next();

		while (rowIt.hasNext()) {
			Row curRow = rowIt.next();

			Iterator<Cell> colIt = curRow.cellIterator();

			String courseLevelStr = colIt.next().getStringCellValue();

			if (StringUtils.isBlank(courseLevelStr)) {
				return;
			}

			CourseDesignator courseDesig = getCourseDesig(courseLevelStr, colIt
					.next().getStringCellValue());

			String jobName = colIt.next().getStringCellValue();
			String jobDescription = colIt.next().getStringCellValue();
			colIt.next();

			Jobs job = new Jobs();
			job.setJobDesc(jobDescription);
			job.setJobDesignation(jobName);
			job.setCourseDesigntator(courseDesig);

			template.save(job);

		}

	}

	private void createRelationShips() {

		CourseLevelEnum courseLevelEnum = CourseLevelEnum.INTERMEDIATE;

		while (courseLevelEnum != CourseLevelEnum.NONE) {
			List<CourseDesignator> courseDesigList = courseDesigRepo
					.findByCourseLevel(courseLevelEnum);
			for (CourseDesignator courseDesignator : courseDesigList) {

				List<MajorCombination> curMajorCombinations = majorCombRepo
						.findByCourseDesignator(courseDesignator);
				List<MajorCombination> nextMajorCombinations = getNextMajorCombRelation(courseDesignator);
				createMajorRelationShip(curMajorCombinations,
						nextMajorCombinations);
			}
			courseLevelEnum = CourseLevelEnum
					.nextValidCourseLevel(courseLevelEnum);
		}

	}

	private void createMajorRelationShip(
			List<MajorCombination> curMajorCombinations,
			List<MajorCombination> nextMajorCombinations) {

		for (MajorCombination curMajorCombination : curMajorCombinations) {
			createJobsRelation(curMajorCombination);
			for (MajorCombination nextMajorCombination : nextMajorCombinations) {
				if (nextMajorCombination.isHasEntranceTest()) {
					createEntranceRelation(curMajorCombination,
							nextMajorCombination);

				} else {

					template.fetch(curMajorCombination.getNextCourses());
					curMajorCombination.getNextCourses().add(
							nextMajorCombination);
					template.save(curMajorCombination);

				}

			}
		}

	}

	private void createEntranceRelation(MajorCombination srcMajor,
			MajorCombination targetMajor) {

		List<EntranceTest> tests = entranceTestRepo
				.findByCourseDesigntatorAndForCourse(
						targetMajor.getCourseDesigntator(),
						targetMajor.getCourseIdentifier());
		for (EntranceTest entranceTest : tests) {
			template.fetch(srcMajor.getEntranceTests());
			srcMajor.getEntranceTests().add(entranceTest);
			template.save(srcMajor);

			template.fetch(entranceTest.getNextCourses());
			entranceTest.getNextCourses().add(targetMajor);
			template.save(entranceTest);
		}

		// template.save(targetMajor);

	}

	private void createJobsRelation(MajorCombination srcMajor) {

		List<Jobs> jobsList = jobRepository.findByCourseDesigntator(srcMajor
				.getCourseDesigntator());
		template.fetch(srcMajor.getPossibleJobs());

		for (Jobs jobs : jobsList) {
			srcMajor.getPossibleJobs().add(jobs);
		}

		template.save(srcMajor);
	}

	private List<MajorCombination> getNextMajorCombRelation(
			CourseDesignator curCourseDesig) {

		List<CourseDesignator> nextCourseDesigs = nextValidCourseDesignators(curCourseDesig);

		List<MajorCombination> majorCombinations = new ArrayList<MajorCombination>();

		for (CourseDesignator courseDesig : nextCourseDesigs) {
			majorCombinations.addAll(majorCombRepo
					.findByCourseDesignator(courseDesig));
		}
		return majorCombinations;
	}

	private List<CourseDesignator> nextValidCourseDesignators(
			CourseDesignator curCourseDesig) {

		List<CourseDesignator> nextCourseDesig = new ArrayList<CourseDesignator>();

		CourseLevelEnum nextCourseLevel = CourseLevelEnum
				.nextValidCourseLevel(curCourseDesig.getCourseLevel());

		if (nextCourseLevel != CourseLevelEnum.NONE) {
			List<CourseStreamEnum> nextCourseStreams = CourseStreamEnum
					.nextValidStreams(curCourseDesig.getCourseLevel(),
							curCourseDesig.getCourseStream());
			for (CourseStreamEnum nextStreamEnum : nextCourseStreams) {
				nextCourseDesig.add(getCourseDesig(nextCourseLevel,
						nextStreamEnum));
			}

		}
		return nextCourseDesig;
	}

}
